create table XXL_JOB_GROUP
(
    ID           NUMBER(11)          not null
        primary key,
    APP_NAME     NVARCHAR2(64)       not null,
    TITLE        NVARCHAR2(12)       not null,
    ADDRESS_TYPE NUMBER(4) default 0 not null,
    ADDRESS_LIST NCLOB,
    UPDATE_TIME  DATE
)
/

comment on column XXL_JOB_GROUP.APP_NAME is '执行器AppName'
/

comment on column XXL_JOB_GROUP.TITLE is '执行器名称'
/

comment on column XXL_JOB_GROUP.ADDRESS_TYPE is '执行器地址类型：0=自动注册、1=手动录入'
/

comment on column XXL_JOB_GROUP.ADDRESS_LIST is '执行器地址列表，多地址逗号分隔'
/

create table XXL_JOB_INFO
(
    ID                        NUMBER(11)           not null
        primary key,
    JOB_GROUP                 NUMBER(11) default 0 not null,
    JOB_DESC                  NVARCHAR2(255)       not null,
    ADD_TIME                  DATE,
    UPDATE_TIME               DATE,
    AUTHOR                    NVARCHAR2(64),
    ALARM_EMAIL               NVARCHAR2(255),
    SCHEDULE_TYPE             NVARCHAR2(50)        not null,
    SCHEDULE_CONF             NVARCHAR2(128),
    MISFIRE_STRATEGY          NVARCHAR2(50)        not null,
    EXECUTOR_ROUTE_STRATEGY   NVARCHAR2(50),
    EXECUTOR_HANDLER          NVARCHAR2(255),
    EXECUTOR_PARAM            NVARCHAR2(512),
    EXECUTOR_BLOCK_STRATEGY   NVARCHAR2(50),
    EXECUTOR_TIMEOUT          NUMBER(11) default 0 not null,
    EXECUTOR_FAIL_RETRY_COUNT NUMBER(11) default 0 not null,
    GLUE_TYPE                 NVARCHAR2(50)        not null,
    GLUE_SOURCE               NCLOB,
    GLUE_REMARK               NVARCHAR2(128),
    GLUE_UPDATETIME           DATE,
    CHILD_JOBID               NVARCHAR2(255),
    TRIGGER_STATUS            NUMBER(4)  default 0 not null,
    TRIGGER_LAST_TIME         NUMBER(20) default 0 not null,
    TRIGGER_NEXT_TIME         NUMBER(20) default 0 not null
)
/

comment on column XXL_JOB_INFO.JOB_GROUP is '执行器主键ID'
/

comment on column XXL_JOB_INFO.AUTHOR is '作者'
/

comment on column XXL_JOB_INFO.ALARM_EMAIL is '报警邮件'
/

comment on column XXL_JOB_INFO.SCHEDULE_TYPE is '调度类型'
/

comment on column XXL_JOB_INFO.SCHEDULE_CONF is '调度配置，值含义取决于调度类型'
/

comment on column XXL_JOB_INFO.MISFIRE_STRATEGY is '调度过期策略'
/

comment on column XXL_JOB_INFO.EXECUTOR_ROUTE_STRATEGY is '执行器路由策略'
/

comment on column XXL_JOB_INFO.EXECUTOR_HANDLER is '执行器任务handler'
/

comment on column XXL_JOB_INFO.EXECUTOR_PARAM is '执行器任务参数'
/

comment on column XXL_JOB_INFO.EXECUTOR_BLOCK_STRATEGY is '阻塞处理策略'
/

comment on column XXL_JOB_INFO.EXECUTOR_TIMEOUT is '任务执行超时时间，单位秒'
/

comment on column XXL_JOB_INFO.EXECUTOR_FAIL_RETRY_COUNT is '失败重试次数'
/

comment on column XXL_JOB_INFO.GLUE_TYPE is 'GLUE类型'
/

comment on column XXL_JOB_INFO.GLUE_SOURCE is 'GLUE源代码'
/

comment on column XXL_JOB_INFO.GLUE_REMARK is 'GLUE备注'
/

comment on column XXL_JOB_INFO.GLUE_UPDATETIME is 'GLUE更新时间'
/

comment on column XXL_JOB_INFO.CHILD_JOBID is '子任务ID，多个逗号分隔'
/

comment on column XXL_JOB_INFO.TRIGGER_STATUS is '调度状态：0-停止，1-运行'
/

comment on column XXL_JOB_INFO.TRIGGER_LAST_TIME is '上次调度时间'
/

comment on column XXL_JOB_INFO.TRIGGER_NEXT_TIME is '下次调度时间'
/

create table XXL_JOB_LOCK
(
    LOCK_NAME NVARCHAR2(50) not null
        primary key
)
/

comment on column XXL_JOB_LOCK.LOCK_NAME is '锁名称'
/

create table XXL_JOB_LOG
(
    ID                        NUMBER(20)           not null
        primary key,
    JOB_GROUP                 NUMBER(11) default 0 not null,
    JOB_ID                    NUMBER(11)           not null,
    EXECUTOR_ADDRESS          NVARCHAR2(255),
    EXECUTOR_HANDLER          NVARCHAR2(255),
    EXECUTOR_PARAM            NVARCHAR2(512),
    EXECUTOR_SHARDING_PARAM   NVARCHAR2(20),
    EXECUTOR_FAIL_RETRY_COUNT NUMBER(11) default 0 not null,
    TRIGGER_TIME              DATE,
    TRIGGER_CODE              NUMBER(11) default 0 not null,
    TRIGGER_MSG               NCLOB,
    HANDLE_TIME               DATE,
    HANDLE_CODE               NUMBER(11) default 0 not null,
    HANDLE_MSG                NCLOB,
    ALARM_STATUS              NUMBER(4)  default 0 not null
)
/

comment on column XXL_JOB_LOG.JOB_GROUP is '执行器主键ID'
/

comment on column XXL_JOB_LOG.JOB_ID is '任务，主键ID'
/

comment on column XXL_JOB_LOG.EXECUTOR_ADDRESS is '执行器地址，本次执行的地址'
/

comment on column XXL_JOB_LOG.EXECUTOR_HANDLER is '执行器任务handler'
/

comment on column XXL_JOB_LOG.EXECUTOR_PARAM is '执行器任务参数'
/

comment on column XXL_JOB_LOG.EXECUTOR_SHARDING_PARAM is '执行器任务分片参数，格式如 1/2'
/

comment on column XXL_JOB_LOG.EXECUTOR_FAIL_RETRY_COUNT is '失败重试次数'
/

comment on column XXL_JOB_LOG.TRIGGER_TIME is '调度-时间'
/

comment on column XXL_JOB_LOG.TRIGGER_CODE is '调度-结果'
/

comment on column XXL_JOB_LOG.TRIGGER_MSG is '调度-日志'
/

comment on column XXL_JOB_LOG.HANDLE_TIME is '执行-时间'
/

comment on column XXL_JOB_LOG.HANDLE_CODE is '执行-状态'
/

comment on column XXL_JOB_LOG.HANDLE_MSG is '执行-日志'
/

comment on column XXL_JOB_LOG.ALARM_STATUS is '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败'
/

create index "I_trigger_time"
    on XXL_JOB_LOG (TRIGGER_TIME)
/

create index "I_handle_code"
    on XXL_JOB_LOG (HANDLE_CODE)
/

create table XXL_JOB_LOG_REPORT
(
    ID            NUMBER(11)           not null
        primary key,
    TRIGGER_DAY   DATE,
    RUNNING_COUNT NUMBER(11) default 0 not null,
    SUC_COUNT     NUMBER(11) default 0 not null,
    FAIL_COUNT    NUMBER(11) default 0 not null,
    UPDATE_TIME   DATE
)
/

comment on column XXL_JOB_LOG_REPORT.TRIGGER_DAY is '调度-时间'
/

comment on column XXL_JOB_LOG_REPORT.RUNNING_COUNT is '运行中-日志数量'
/

comment on column XXL_JOB_LOG_REPORT.SUC_COUNT is '执行成功-日志数量'
/

comment on column XXL_JOB_LOG_REPORT.FAIL_COUNT is '执行失败-日志数量'
/

create unique index "i_trigger_day"
    on XXL_JOB_LOG_REPORT (TRIGGER_DAY)
/

create table XXL_JOB_LOGGLUE
(
    ID          NUMBER(11)     not null
        primary key,
    JOB_ID      NUMBER(11)     not null,
    GLUE_TYPE   NVARCHAR2(50),
    GLUE_SOURCE NCLOB,
    GLUE_REMARK NVARCHAR2(128) not null,
    ADD_TIME    DATE,
    UPDATE_TIME DATE
)
/

comment on column XXL_JOB_LOGGLUE.JOB_ID is '任务，主键ID'
/

comment on column XXL_JOB_LOGGLUE.GLUE_TYPE is 'GLUE类型'
/

comment on column XXL_JOB_LOGGLUE.GLUE_SOURCE is 'GLUE源代码'
/

comment on column XXL_JOB_LOGGLUE.GLUE_REMARK is 'GLUE备注'
/

create table XXL_JOB_REGISTRY
(
    ID             NUMBER(11)     not null
        primary key,
    REGISTRY_GROUP NVARCHAR2(50)  not null,
    REGISTRY_KEY   NVARCHAR2(255) not null,
    REGISTRY_VALUE NVARCHAR2(255) not null,
    UPDATE_TIME    DATE
)
/

create index "i_g_k_v"
    on XXL_JOB_REGISTRY (REGISTRY_GROUP, REGISTRY_KEY, REGISTRY_VALUE)
/

create table XXL_JOB_USER
(
    ID         NUMBER(11)    not null
        primary key,
    USERNAME   NVARCHAR2(50) not null,
    PASSWORD   NVARCHAR2(50) not null,
    ROLE       NUMBER(4)     not null,
    PERMISSION NVARCHAR2(255)
)
/

comment on column XXL_JOB_USER.USERNAME is '账号'
/

comment on column XXL_JOB_USER.PASSWORD is '密码'
/

comment on column XXL_JOB_USER.ROLE is '角色：0-普通用户、1-管理员'
/

comment on column XXL_JOB_USER.PERMISSION is '权限：执行器ID列表，多个逗号分割'
/

create unique index "i_username"
    on XXL_JOB_USER (USERNAME)
/

create table INTERFACE_INFO
(
    INTERFACE_NO VARCHAR2(20) not null
            primary key,
    URL VARCHAR2(500) not null,
    NAME VARCHAR2(200) not null,
    DESCRIBE VARCHAR2(300),
    ACCOUNT VARCHAR2(100),
    PASSWORD VARCHAR2(100),
    USE_YN VARCHAR2(1) not null,
    REG_USR_ID VARCHAR2(20) not null,
    REG_DT DATE not null,
    UPD_USR_ID VARCHAR2(20),
    UPD_DT DATE
)
/

comment on table INTERFACE_INFO is '接口地址表'
/

comment on column INTERFACE_INFO.INTERFACE_NO is '接口编号'
/

comment on column INTERFACE_INFO.URL is '接口地址'
/

comment on column INTERFACE_INFO.NAME is '名称'
/

comment on column INTERFACE_INFO.DESCRIBE is '描述'
/

comment on column INTERFACE_INFO.ACCOUNT is '账号'
/

comment on column INTERFACE_INFO.PASSWORD is '密码'
/

comment on column INTERFACE_INFO.USE_YN is '是否有效'
/

comment on column INTERFACE_INFO.REG_USR_ID is '创建人主键'
/

comment on column INTERFACE_INFO.REG_DT is '创建时间'
/

comment on column INTERFACE_INFO.UPD_USR_ID is '更新人主键'
/

comment on column INTERFACE_INFO.UPD_DT is '更新时间'
/



create sequence XXL_JOB_USER_ID
    start with 1
    increment by 1
    maxvalue 999999999;
create sequence XXL_JOB_REGISTRY_ID
    start with 1
    increment by 1
    maxvalue 999999999;
create sequence XXL_JOB_LOG_REPORT_ID
    start with 1
    increment by 1
    maxvalue 999999999;
create sequence XXL_JOB_LOG_ID
    start with 1
    increment by 1
    maxvalue 999999999;
create sequence XXL_JOB_LOGGLUE_ID
    start with 1
    increment by 1
    maxvalue 999999999;
create sequence XXL_JOB_GROUP_ID
    start with 1
    increment by 1
    maxvalue 999999999;
create sequence XXL_JOB_INFO_ID
    start with 1
    increment by 1
    maxvalue 999999999;


INSERT INTO XXL_JOB_USER (ID, USERNAME, PASSWORD, ROLE, PERMISSION)
VALUES (XXL_JOB_USER_ID.nextval, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, null);
INSERT INTO xxl_job_lock( lock_name)
VALUES ('schedule_lock');
INSERT INTO xxl_job_group(id, app_name, title, address_type, address_list, update_time)
VALUES (1, 'xxl-job-executor-sample', '示例执行器', 0, NULL, '2018-11-03 22:21:31' );
